package com.zzptc.myk.shop.dao;


import com.zzptc.myk.shop.db.DBI;
import com.zzptc.myk.shop.db.DBImpl;
import com.zzptc.myk.shop.vo.Category;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class CategroryDaoImpl implements CategroryDaoI {

	/**
	 * 查询出所有类别
	 * @return
	 * @throws SQLException
	 */
	public List<Category> getAll() throws SQLException {
		// TODO Auto-generated method stub
		DBI db = new DBImpl();
		String sql1="SELECT * FROM taotaobuy_product_category;";
		Connection connection = db.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		ps = connection.prepareStatement(sql1);
		rs= ps.executeQuery();
		List<Category> listbig = new ArrayList<Category>();
		while(rs.next()){
			Category category = new Category();
			category.setTpcId(rs.getInt(1));
			category.setTpcName(rs.getString(2));
			category.setTpcParentId(rs.getInt(3));

			listbig.add(category);

		}
		db.closeAll(null,ps,rs);
		db.closeConn();
		return listbig;
	}
	//将大小类全部查询出来不需要判断直接显示
	public List<Category> getProduct(int page, int pageCount)
			throws SQLException {
		List<Category> list = new ArrayList<Category>();
		DBI db = new DBImpl();
		Connection con = db.getConn();
		String sql = "select t1.* , case when t2.epc_id is null then t1.epc_id else t2.epc_id end pid from  easybuy_product_category t1 "+
				" left join  taotaobuy_product_category t2 "+
				" on t1.epc_parent_id = t2.epc_id "+
				" order by pid, t1.epc_parent_id, t1.epc_id LIMIT ?,?;";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, (page-1)*pageCount);
		ps.setInt(2, pageCount);
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			Category category = new Category();
			category.setTpcId(rs.getInt(1));
			category.setTpcName(rs.getString(2));
			category.setTpcParentId(rs.getInt(3));
			list.add(category);
		}
		db.closeAll(null,ps,rs);
		db.closeConn();
		return list;
		
	}
//	//得到数据库所有数据总数 用于设置页数
//	public int getCount()throws SQLException{
//		DBI db = new DBImpl();
//		Connection con = db.getConn();
//		int count = 0;
//		String sql = "SELECT COUNT(*) FROM easybuy_product_category;";
//		PreparedStatement ps = conn.prepareStatement(sql);
//		ResultSet rs = ps.executeQuery();
//		if(rs.next()){
//			count = rs.getInt(1);
//		}
//		return count;
//	}
	
	//删除
		public int delete(int id,int parentid ) throws SQLException {
			// TODO Auto-generated method stub
			DBI db = new DBImpl();
			Connection con = db.getConn();
			int flag = 0;
			Connection con1 = null;
			PreparedStatement ps =null;
			PreparedStatement ps1 =null;
			PreparedStatement ps2 =null;
			if(parentid != 0){
			con = db.getConn();
			String sql = "DELETE FROM taotaobuy_product_category WHERE epc_id = ?;";
			ps = con.prepareStatement(sql);
			ps.setInt(1, id);
			flag = ps.executeUpdate();

			ps.close();
			}else{

			String sql2= "DELETE FROM taotaobuy_product_category WHERE epc_parent_id="+id;	//删除子结点
			String sql1 = "DELETE FROM taotaobuy_product_category WHERE epc_id = "+id;//删除自己
			con = db.getConn();
			con.setAutoCommit(false);

			ps1 = con.prepareStatement(sql2);
			ps2 = con.prepareStatement(sql1);

			flag = ps1.executeUpdate();
			flag = ps2.executeUpdate();

			con.commit();

			ps2.close();
			ps1.close();
			}


			db.closeConn();
			return flag;
		}

	@Override
	public int updateProductClass(int id, Category category) throws Exception {
		return 0;
	}

//	//修改分类表
//		public int updateProductClass(int id, Category category) throws Exception {
//			// TODO Auto-generated method stub
//			DB db = new DB();
//			Connection conn = db.getConn();
//			String sql = "UPDATE  easybuy_product_category SET epc_name = ? WHERE epc_id = "+id;
//			PreparedStatement ps = conn.prepareStatement(sql);
//			ps.setString(1, category.getEpc_name());
//			int a = ps.executeUpdate();
//			ps.close();
//			db.closeConn();
//			return a;
//		}
//
		public boolean insert(Category category,int id) throws SQLException {
			// TODO Auto-generated method stub
			DBI db = new DBImpl();
			Connection con = db.getConn();
			String sql = "INSERT INTO taotaobuy_product_category(epc_name,epc_parent_id )VALUES(?,0)";
			String sql1 = "INSERT INTO taotaobuy_product_category(epc_name,epc_parent_id )VALUES(?,?);";
			int a = 0;
			PreparedStatement ps = null,ps1 = null;
			if(id == 0){
				ps = con.prepareStatement(sql);
				ps.setString(1, category.getTpcName());
				a = ps.executeUpdate();
			}else{
				ps1 = con.prepareStatement(sql1);
				ps1.setString(1, category.getTpcName());
				ps1.setInt(2, id);
				a = ps1.executeUpdate();
			}
			return a > 0;
		}
		
	public static void main(String[] args) {
		int id = 19, idd = 0;
		String name = null;
		CategroryDaoImpl dao = new CategroryDaoImpl();
		try {
			System.out.println(dao.getAll().size());
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}
		List<Category> list = new ArrayList<Category>();
		Category category = new Category();
		//category.setEpc_name("大米");
		int page = 1,pageCount = 0;
		
//		try {
//			pageCount = dao.getCount();
//
//			list = dao.getProduct(page, pageCount);
//			for(int a=0;a<list.size();a++){
//				Category category2 = list.get(a);
//				System.out.println(category2.getEpc_id()+":"+category2.getEpc_name());
//			}
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//
		
		/*try {
		boolean a = dao.insert(category, id);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}*/
//
//		try {
//			list = dao.getAll();
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//		for (int a = 0; a < list.size(); a++) {
//			category = list.get(a);
//			List<Category> list1 = new ArrayList<Category>();
//			list1 = category.getList();
//			
//			for (int c = 0; c < list1.size(); c++) {
//				Category category2 = list1.get(c);
//				if(idd != category.getEpc_parent_id()){
//				name = category2.getEpc_name();
//				id = category2.getEpc_id();
//				System.out.println(name + "***2***" + id);
//				}else{
//					
//				}
//				
//			}
//		}
	}
	
}
